package com.zhiyi.xppay.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.zhiyi.item.CpReqData;
import com.zhiyi.xppay.hook.CustomApplcation;

import java.text.DecimalFormat;
import java.util.ArrayList;

/**
 * 

* @ClassName: DBManager

* @Description: TODO(这里用一句话描述这个类的作用)

* @author SuXiaoliang

* @date 2018年6月23日 下午1:27:22

*
 */
public class DBManager {
	private SQLiteDatabase db;
	private DBHelper helper;
	public DBManager(Context context){
        helper = new DBHelper(context);
        db = helper.getWritableDatabase();

    }

	public boolean isExistAliBOrder(String no) {
		boolean isExist=false;
		String sql = "SELECT * FROM alibankorder WHERE billno='"+no+"'";
		Cursor c = ExecSQLForCursor(sql);
		if(c.getCount()>0){
			isExist=true;
		}
		c.close();
		return isExist;
	}

    // 云闪付
	public void addCPOrder(CpReqData cpReqData) {
		db.beginTransaction();// 开始事务
		try {
			db.execSQL("INSERT INTO cpcheckorder VALUES(null,?,?,?)", new Object[] { cpReqData.remark(),cpReqData.orderid,cpReqData.time});
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}

	public boolean isExistCPOrder(String checkorderid) {
		boolean isExist=false;
		String sql = "SELECT * FROM cpcheckorder WHERE checkorder='"+checkorderid+"'";
		Cursor c = ExecSQLForCursor(sql);
		if(c.getCount()>0){
			isExist=true;
		}
		c.close();
		return isExist;
	}


	public ArrayList<CpReqData> FindCPOrder(String orderid) {
		String sql = "SELECT * FROM cpcheckorder WHERE checkorder =" + "'" + orderid+ "'";
		ArrayList<CpReqData> datas = new ArrayList<CpReqData>();
		Cursor c = ExecSQLForCursor(sql);
		while (c.moveToNext()) {
			CpReqData info = new CpReqData(c.getString(c.getColumnIndex("remark")));
			datas.add(info);
		}
		c.close();
		return datas;
	}

	public void updateCPOrder(CpReqData cpReqData){
		db.beginTransaction();// 开始事务
		try {
			ContentValues contentValues = new ContentValues();
			contentValues.put("remark",cpReqData.remark());
			contentValues.put("checkorder",cpReqData.orderid);
			db.update("cpcheckorder",contentValues,"remark=?",new String[]{cpReqData.remark()});
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}

	//
    // 设置支付宝信息
	public String getAlipwd(){
		String sql = "SELECT pwd FROM alipayinfo ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		String rs = null;
		if(c.getCount()>0){
			c.moveToNext();
			rs = c.getString(c.getColumnIndex("pwd"));
		}
		c.close();
		return rs;
	}
	public String getAliwithcount(){
		String sql = "SELECT withdrawcount FROM alipayinfo ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		String rs = null;
		if(c.getCount()>0){
			c.moveToNext();
			rs = c.getString(c.getColumnIndex("withdrawcount"));
		}
		c.close();
		return rs;
	}
	public String getBankcardno(){
		String sql = "SELECT bankcardno FROM alipayinfo ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		String rs = null;
		if(c.getCount()>0){
			c.moveToNext();
			rs = c.getString(c.getColumnIndex("bankcardno"));
		}
		c.close();
		return rs;
	}

	public String getBankaccount(){
		String sql = "SELECT bankaccount FROM alipayinfo ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		String rs = null;
		if(c.getCount()>0){
			c.moveToNext();
			rs = c.getString(c.getColumnIndex("bankaccount"));
		}
		c.close();
		return rs;
	}

	public String getAlipayaccount(){
		String sql = "SELECT alipayaccount FROM alipayinfo ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		String rs = null;
		if(c.getCount()>0){
			c.moveToNext();
			rs = c.getString(c.getColumnIndex("alipayaccount"));
		}
		c.close();
		return rs;
	}
	public int getWithdrawtype(){
		String sql = "SELECT withdrawtype FROM alipayinfo ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		int rs = -1;
		if(c.getCount()>0){
			c.moveToNext();
			rs = c.getInt(c.getColumnIndex("withdrawtype"));
		}
		c.close();
		return rs;
	}
	public void setAlipayInfo(String pwd, String withcount, String bankcardno, String bankaccount, String alipayaccount, int withdrawtype){
		db.beginTransaction();// 开始事务
		try {
			if(getAlipwd()==null){
				db.execSQL("INSERT INTO alipayinfo VALUES(null,?,?,?,?,?,?)", new Object[] {pwd,withcount,bankcardno,bankaccount,alipayaccount,withdrawtype});
			}else{
				db.execSQL("UPDATE alipayinfo SET pwd = ?,withdrawcount = ?,bankcardno = ?,bankaccount = ?,alipayaccount = ?,withdrawtype = ?", new Object[] {pwd, withcount,bankcardno,bankaccount,alipayaccount,withdrawtype});
			}
			PayHelperUtils.sendmsg(CustomApplcation.getContext(),"db sava success pwd " + pwd + " withdrawcount " + withcount);
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}
	//
    public String getAppid(){
		String sql = "SELECT appid FROM account ORDER BY _id DESC";
		Cursor c = ExecSQLForCursor(sql);
		String appid = null;
		if(c.getCount()>0){
			c.moveToNext();
			appid = c.getString(c.getColumnIndex("appid"));
		}
		c.close();
		return appid;
	}

	public String getToken(){
		String sql = "SELECT token FROM account ORDER BY _id DESC";
		String token = null;
		Cursor c = ExecSQLForCursor(sql);
		if(c.getCount()>0){
			c.moveToNext();
			token = c.getString(c.getColumnIndex("token"));
		}
		c.close();
		return token;
	}

	public void saveAppidInfo(String appid, String token){
		db.beginTransaction();// 开始事务
		try {
			if(getAppid()==null){
				String dt= System.currentTimeMillis()/1000+"";
				db.execSQL("INSERT INTO account VALUES(null,?,?,?)", new Object[] { appid, token, dt });
			}else{
				db.execSQL("UPDATE account SET appid = ?,token = ?", new Object[] { appid, token });
			}
			PayHelperUtils.sendmsg(CustomApplcation.getContext(),"db sava success appid " + appid + " token " + token);
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}

	 public void addQrCode(QrCodeBean qrCodeBean) {
        db.beginTransaction();// 开始事务
        try {
//        	String dt=System.currentTimeMillis()/1000+"";
			String dt= System.currentTimeMillis()+"";
            db.execSQL("INSERT INTO qrcode VALUES(null,?,?,?,?,?)", new Object[] { qrCodeBean.getMoney(), qrCodeBean.getMark(), qrCodeBean.getType(), qrCodeBean.getPayurl(), dt });
            db.setTransactionSuccessful();// 事务成功
        } finally {
            db.endTransaction();// 结束事务
        }
    }
	public void addOrder(OrderBean ordereBean) {
		 db.beginTransaction();// 开始事务
		 try {
			 db.execSQL("INSERT INTO payorder VALUES(null,?,?,?,?,?,?,?)", new Object[] { ordereBean.getMoney(), ordereBean.getMark(), ordereBean.getType(), ordereBean.getNo(), ordereBean.getDt() ,ordereBean.getResult() , ordereBean.getTime() });
			 db.setTransactionSuccessful();// 事务成功
		 } finally {
			 db.endTransaction();// 结束事务
		 }
	 }
	public void updateTradeStep2(OrderBean ordereBean){
		db.beginTransaction();// 开始事务
		try {
			ContentValues contentValues = new ContentValues();
			contentValues.put("money",ordereBean.getMoney());
			contentValues.put("mark",ordereBean.getMark());
			contentValues.put("dt",ordereBean.getDt());
			contentValues.put("result","2");
			db.update("payorder",contentValues,"tradeno=?",new String[]{ordereBean.getNo()});
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}
	public void updateTradeStep3(String no){
		db.beginTransaction();// 开始事务
		try {
			ContentValues contentValues = new ContentValues();
			contentValues.put("result","3");
			db.update("payorder",contentValues,"tradeno=?",new String[]{no});
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}
	public void updateTradeStep0(String no){
		db.beginTransaction();// 开始事务
		try {
			ContentValues contentValues = new ContentValues();
			contentValues.put("result","0");
			db.update("payorder",contentValues,"tradeno=?",new String[]{no});
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}
	public void addTradeNo(String tradeNo) {
		db.beginTransaction();// 开始事务
		try {
			db.execSQL("INSERT INTO tradeno VALUES(null,?)", new Object[] { tradeNo });
			db.setTransactionSuccessful();// 事务成功
		} finally {
			db.endTransaction();// 结束事务
		}
	}
	
	public boolean isExistTradeNo(String tradeNo) {
		boolean isExist=false;
		String sql = "SELECT * FROM payorder WHERE tradeno='"+tradeNo+"'";
		Cursor c = ExecSQLForCursor(sql);
		if(c.getCount()>0){
			isExist=true;
		}
		c.close();
		return isExist;
	}
	public boolean isFinishedTradeNo(String no){
		boolean isExist=false;
		String sql = "SELECT * FROM payorder WHERE tradeno='"+no+"' AND result IN ('2','3')";
		Cursor c = ExecSQLForCursor(sql);
		if(c.getCount()>0){
			isExist=true;
		}
		c.close();
		return isExist;
	}

	public void updateOrder(String no, String result) {
		 db.beginTransaction();// 开始事务
		 try {
			 db.execSQL("UPDATE payorder SET result=?,time=time+1 WHERE tradeno=?", new Object[] {result, no});
			 db.setTransactionSuccessful();// 事务成功
		 } finally {
			 db.endTransaction();// 结束事务
		 }
	 }
	 
	 public ArrayList<QrCodeBean> FindQrCodes(String money, String mark, String type) {
		DecimalFormat df = new DecimalFormat("0.00");
		money=df.format(Double.parseDouble(money));
    	String sql = "SELECT * FROM qrcode WHERE money =" + "'" + money + "' and mark='"+mark+"' and type='"+type+"'";
        ArrayList<QrCodeBean> list = new ArrayList<QrCodeBean>();
        Cursor c = ExecSQLForCursor(sql);
        while (c.moveToNext()) {
            QrCodeBean info = new QrCodeBean();
            info.setMoney(c.getString(c.getColumnIndex("money")));
            info.setMark(c.getString(c.getColumnIndex("mark")));
            info.setType(c.getString(c.getColumnIndex("type")));
            info.setPayurl(c.getString(c.getColumnIndex("payurl")));
            info.setDt(c.getString(c.getColumnIndex("dt")));
            list.add(info);
        }
        c.close();
        return list;
    }
	 public ArrayList<QrCodeBean> FindQrCodes(String mark, String type) {
		 String sql = "SELECT * FROM qrcode WHERE mark='"+mark+"'";
		 ArrayList<QrCodeBean> list = new ArrayList<QrCodeBean>();
		 Cursor c = ExecSQLForCursor(sql);
		 while (c.moveToNext()) {
			 QrCodeBean info = new QrCodeBean();
			 info.setMoney(c.getString(c.getColumnIndex("money")));
			 info.setMark(c.getString(c.getColumnIndex("mark")));
			 info.setType(c.getString(c.getColumnIndex("type")));
			 info.setPayurl(c.getString(c.getColumnIndex("payurl")));
			 info.setDt(c.getString(c.getColumnIndex("dt")));
			 list.add(info);
		 }
		 c.close();
		 return list;
	 }
	public ArrayList<OrderBean> FindOrders(String money, String mark, String type) {
    	String sql = "SELECT * FROM payorder WHERE money =" + "'" + money + "' and mark='"+mark+"' and type='"+type+"'";
    	ArrayList<OrderBean> list = new ArrayList<OrderBean>();
    	Cursor c = ExecSQLForCursor(sql);
    	while (c.moveToNext()) {
    		OrderBean info = new OrderBean();
    		info.setMoney(c.getString(c.getColumnIndex("money")));
    		info.setMark(c.getString(c.getColumnIndex("mark")));
    		info.setType(c.getString(c.getColumnIndex("type")));
    		info.setNo(c.getString(c.getColumnIndex("tradeno")));
    		info.setDt(c.getString(c.getColumnIndex("dt")));
    		info.setResult(c.getString(c.getColumnIndex("result")));
    		info.setTime(c.getInt(c.getColumnIndex("time")));
    		list.add(info);
    	}
    	c.close();
    	return list;
    }
	public ArrayList<OrderBean> FindOrders(String mark) {
		String sql = "SELECT * FROM payorder WHERE mark='"+mark+"'";
		ArrayList<OrderBean> list = new ArrayList<OrderBean>();
		Cursor c = ExecSQLForCursor(sql);
		while (c.moveToNext()) {
			OrderBean info = new OrderBean();
			info.setMoney(c.getString(c.getColumnIndex("money")));
			info.setMark(c.getString(c.getColumnIndex("mark")));
			info.setType(c.getString(c.getColumnIndex("type")));
			info.setNo(c.getString(c.getColumnIndex("tradeno")));
			info.setDt(c.getString(c.getColumnIndex("dt")));
			info.setResult(c.getString(c.getColumnIndex("result")));
    		info.setTime(c.getInt(c.getColumnIndex("time")));
			list.add(info);
		}
		c.close();
		return list;
	}
	public ArrayList<OrderBean> FindOrdersByNo(String no) {
		String sql = "SELECT * FROM payorder WHERE tradeno='"+no+"'";
		ArrayList<OrderBean> list = new ArrayList<OrderBean>();
		Cursor c = ExecSQLForCursor(sql);
		while (c.moveToNext()) {
			OrderBean info = new OrderBean();
			info.setMoney(c.getString(c.getColumnIndex("money")));
			info.setMark(c.getString(c.getColumnIndex("mark")));
			info.setType(c.getString(c.getColumnIndex("type")));
			info.setNo(c.getString(c.getColumnIndex("tradeno")));
			info.setDt(c.getString(c.getColumnIndex("dt")));
			info.setResult(c.getString(c.getColumnIndex("result")));
			info.setTime(c.getInt(c.getColumnIndex("time")));
			list.add(info);
		}
		c.close();
		return list;
	}
	public ArrayList<OrderBean> FindAllOrders() {
//		String sql = "SELECT * FROM payorder where result <> 'success' and time<3 ";
		String sql = "SELECT * FROM payorder where result = '2' and time<3 ";
		ArrayList<OrderBean> list = new ArrayList<OrderBean>();
		Cursor c = ExecSQLForCursor(sql);
		while (c.moveToNext()) {
			OrderBean info = new OrderBean();
			info.setMoney(c.getString(c.getColumnIndex("money")));
			info.setMark(c.getString(c.getColumnIndex("mark")));
			info.setType(c.getString(c.getColumnIndex("type")));
			info.setNo(c.getString(c.getColumnIndex("tradeno")));
			info.setDt(c.getString(c.getColumnIndex("dt")));
			info.setResult(c.getString(c.getColumnIndex("result")));
			info.setTime(c.getInt(c.getColumnIndex("time")));
			list.add(info);
		}
		c.close();
		return list;
	}

	public ArrayList<OrderBean> FindReuslt0Orders() {
		String sql = "SELECT * FROM payorder where result = '0' and time<3 ";
		ArrayList<OrderBean> list = new ArrayList<OrderBean>();
		Cursor c = ExecSQLForCursor(sql);
		while (c.moveToNext()) {
			OrderBean info = new OrderBean();
			info.setMoney(c.getString(c.getColumnIndex("money")));
			info.setMark(c.getString(c.getColumnIndex("mark")));
			info.setType(c.getString(c.getColumnIndex("type")));
			info.setNo(c.getString(c.getColumnIndex("tradeno")));
			info.setDt(c.getString(c.getColumnIndex("dt")));
			info.setResult(c.getString(c.getColumnIndex("result")));
			info.setTime(c.getInt(c.getColumnIndex("time")));
			list.add(info);
		}
		c.close();
		return list;
	}
	    
    /**
     * 执行SQL，返回一个游标
     * 
     * @param sql
     * @return
     */
    private Cursor ExecSQLForCursor(String sql) {
        Cursor c = db.rawQuery(sql, null);
        return c;
    }
}
